package cn.com.nes.site.module.dao.crm;

import cn.com.nes.common.util.*;
import cn.com.nes.site.entity.DataReq;
import cn.com.nes.site.entity.PageData;
import cn.com.nes.site.module.dao.BaseDaoImpl;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.fastjson2.JSONObject;
import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.TransactionStatus;

import java.math.BigDecimal;
import java.sql.Connection;
import java.text.SimpleDateFormat;
import java.util.*;

@Repository
public class WorkbenchDaoImpl extends BaseDaoImpl {
    private TransactionStatus newg_status = null;

    public PageData getDataOverview(DataReq req) {
        Map<String, Object> param = req.getReqData();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        Map<String, Object> extData = new HashMap<String, Object>();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("获取产品信息失败");
        try {
            StringBuffer sb1 = new StringBuffer();
            //区分是否为管理员 '04'是管理员
            String userType=req.getUinfo().getUserType();
            if(!"04".equals(userType)){//不是管理员公海不加线索，我的客户自己看自己的
                sb1.append("  and ((1=1 ");
                sb1.append(XzqhUtil.getMultipleQueryXzqh(this.getJdbcTemplate_newg(), req.getUinfo().getAreacode(), "a.xzqhdm"));
                sb1.append(" ) or a.clueowner='").append(req.getUinfo().getId()).append("' ) ");
            }

            StringBuffer sb = new StringBuffer();
            if (StringUtils.isEmpty((String) param.get("dateType"))) {
                res.setRetCode(9);
                res.setRetMessage("日期类型不能为空! ");
                return res;
            }
            String dateType = String.valueOf(param.get("dateType"));
            String sbform1 = dateTypeCondition_mod(dateType);
            String sbform2 = dateTypeCondition(dateType);

            //获取新增客户数量
//            sb.delete(0, sb.length());
//            sb.append("select count(1) cnt from customer a where a.isdelete=0 and a.companyId like concat('"+req.getUinfo().getCompanyId()+"','%')");
//            sb.append(sbform);
//            sb = DBUtils.sql_check(sb) ;
//            List<Map<String, Object>> newCustomerList = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{});
//            extData.put("newCustomerCount", newCustomerList.get(0).get("cnt"));

            //获取新增客户数量（已改为认领线索）
            sb.delete(0, sb.length());
            sb.append("select count(1) cnt from clue a where sfzx='1' and a.isdelete=0 and a.clueowner is not null ");
            sb.append(" and a.cluebelongtocompany like concat('").append(req.getUinfo().getCompanyId()).append("','%') ");
            sb.append(" and a.clueowner =  '").append(req.getUinfo().getId()).append("' ");
            sb.append(sbform1).append(sb1);
            sb = DBUtils.sql_check(sb);
            List<Map<String, Object>> newCustomerList = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{});
            extData.put("newCustomerCount", newCustomerList.get(0).get("cnt"));

            //获取新增线索数量
            sb.delete(0, sb.length());
            sb.append("select count(1) cnt from clue a where sfzx='1' and a.isdelete=0 ");
            sb.append(" and a.cluebelongtocompany like concat('").append(req.getUinfo().getCompanyId()).append("','%') ");
            sb.append(sbform1).append(sb1);
            sb = DBUtils.sql_check(sb);
            List<Map<String, Object>> newClueList = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{});
            extData.put("newClueCount", newClueList.get(0).get("cnt"));

            //新认领线索
            sb.delete(0, sb.length());
            sb.append("select count(1) cnt from clue a where sfzx='1' and a.isdelete=0 and a.clueowner is not null ");
            sb.append(" and a.cluebelongtocompany like concat('").append(req.getUinfo().getCompanyId()).append("','%') ");
            sb.append(" and a.clueowner =  '").append(req.getUinfo().getId()).append("' ");
            sb.append(sbform1).append(sb1);
            sb = DBUtils.sql_check(sb);
            List<Map<String, Object>> newClaimClueList = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{});
            extData.put("newClaimClueCount", newClaimClueList.get(0).get("cnt"));

            //新增商机
            sb.delete(0, sb.length());
            sb.append("select count(1) cnt from opportunity a where a.isdelete=0 and a.companyId like concat('" + req.getUinfo().getCompanyId() + "','%') ");
            if(!"04".equals(userType)){
                sb.append(" and a.opportunityowner='").append(req.getUinfo().getId()).append("' ");
            }
            sb.append(sbform1);
            sb = DBUtils.sql_check(sb);
            List<Map<String, Object>> newOpportunityList = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{});
            extData.put("newOpportunityCount", newOpportunityList.get(0).get("cnt"));

            //客户拜访
            sb.delete(0, sb.length());
            sb.append("select count(1) cnt from log_claim a where a.isdelete=0 and a.companyId like concat('" + req.getUinfo().getCompanyId() + "','%')");
            if(!"04".equals(userType)){
                sb.append(" and a.userid='").append(req.getUinfo().getId()).append("' ");
            }
            sb.append(sbform2);
            sb = DBUtils.sql_check(sb);
            List<Map<String, Object>> customerClaimList = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{});
            extData.put("customerClaimCount", customerClaimList.get(0).get("cnt"));

            //电话联系
            sb.delete(0, sb.length());
            sb.append("select count(1) cnt from log_claim a where a.isdelete=0 and a.visittype='01' and a.companyId like concat('" + req.getUinfo().getCompanyId() + "','%') ");
            if(!"04".equals(userType)){
                sb.append(" and a.userid='").append(req.getUinfo().getId()).append("' ");
            }
            sb.append(sbform2);
            sb = DBUtils.sql_check(sb);
            List<Map<String, Object>> phoneContractList = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{});
            extData.put("phoneContractCount", phoneContractList.get(0).get("cnt"));

            //新增报价单
            sb.delete(0, sb.length());
            sb.append("select count(1) cnt from opportunity_quote a left join opportunity b on a.opportunityId=b.uuid where a.isdelete=0 and b.companyId like concat('" + req.getUinfo().getCompanyId() + "','%') ");
            if(!"04".equals(userType)){
                sb.append(" and a.createUid='").append(req.getUinfo().getId()).append("' ");
            }
            sb.append(sbform2);
            sb = DBUtils.sql_check(sb);
            List<Map<String, Object>> newQuoteList = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{});
            extData.put("newQuoteCount", newQuoteList.get(0).get("cnt"));

            //客户联系人
            sb.delete(0, sb.length());
            sb.append("select count(1) cnt from log_operate a where a.remark like '%增加客户联系人%' and a.companyId like concat('" + req.getUinfo().getCompanyId() + "','%') ");
            if(!"04".equals(userType)){
                sb.append(" and a.userid='").append(req.getUinfo().getId()).append("' ");
            }
            sb.append(sbform2);
            sb = DBUtils.sql_check(sb);
            List<Map<String, Object>> newStaffList = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{});
            extData.put("newStaffCount", newStaffList.get(0).get("cnt"));
            res.setExtData(extData);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

        }
        return res;
    }

    public PageData getCustomerStatus(DataReq req) {
        Map<String, Object> param = req.getReqData();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        Map<String, Object> extData = new HashMap<String, Object>();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("获取客户状态失败");
        StringBuffer sb1 = new StringBuffer();
        StringBuffer sb2 = new StringBuffer();
        try {
            StringBuffer sb = new StringBuffer();
            if (StringUtils.isEmpty((String) param.get("dateType"))) {
                res.setRetCode(9);
                res.setRetMessage("日期类型不能为空! ");
                return res;
            }
            String dateType = String.valueOf(param.get("dateType"));
            String sbform = dateTypeCondition(dateType);
            //区分是否为管理员 '04'是管理员
            String userType=req.getUinfo().getUserType();
            if(!"04".equals(userType)){
                sb1.append(" and a.userid='").append(req.getUinfo().getId()).append("' ");
            }
            if(!"04".equals(userType)){
                sb2.append(" and ((1=1  ");
                sb2.append(XzqhUtil.getMultipleQueryXzqh(this.getJdbcTemplate_newg(), req.getUinfo().getAreacode(), "a.xzqhdm"));
                sb2.append(" ) ");
                sb2.append(" or a.clueowner='").append(req.getUinfo().getId()).append("' ");
                sb2.append(" ) ");
                sb2.append("  and a.clueowner =  '").append(req.getUinfo().getId()).append("' ");
            }
            //获取客户状态
            sb.delete(0, sb.length());
            sb.append("select (select count(1) from log_claim a where a.isdelete=0 and a.companyId like concat('" + req.getUinfo().getCompanyId() + "','%') "); //有进展
            sb.append(sbform).append(sb1);
            sb.append(") yjz, (select count(1) from clue a  where a.isdelete=0 and a.cluestage='02' and a.cluebelongtocompany like concat('" + req.getUinfo().getCompanyId() + "','%') "); //明确意向
            sb.append(sbform).append(sb2);
            sb.append(") mqyx, (select count(1) from clue a where a.isdelete=0 and a.cluestage='04' and a.cluebelongtocompany like concat('" + req.getUinfo().getCompanyId() + "','%')  "); //转商机
            sb.append(sbform).append(sb2);
            sb.append(") zsj, (select count(distinct a.uuid) from customer a inner join customer_staff b on a.uuid=b.customer_id where a.isdelete=0 and b.deleted=0 and a.company_id like concat('" + req.getUinfo().getCompanyId() + "','%') "); //添加联系方式
            sb.append(sbform);
            sb.append(") tjlxfs, (select count(1) from clue a where a.isdelete=0 and a.cluestage='05' and a.cluebelongtocompany like concat('" + req.getUinfo().getCompanyId() + "','%') "); //无合作意向
            sb.append(sbform).append(sb2);
            sb.append(") whzyx ");
            sb = DBUtils.sql_check(sb);
            List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{});
            res.setExtData(extData);
            res.setData(list.get(0));
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

        }
        return res;
    }

    public PageData getWillDeal(DataReq req) {
        Map<String, Object> param = req.getReqData();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        Map<String, Object> extData = new HashMap<String, Object>();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("获取待办失败");
        try {
            StringBuffer sb = new StringBuffer();
            StringBuffer sb1 = new StringBuffer();
            //区分是否为管理员 '04'是管理员
            String userType=req.getUinfo().getUserType();
            if(!"04".equals(userType)){
                sb1.append(" and ((1=1  ");
                sb1.append(XzqhUtil.getMultipleQueryXzqh(this.getJdbcTemplate_newg(), req.getUinfo().getAreacode(), "a.xzqhdm"));
                sb1.append(" ) ");
                sb1.append(" or a.clueowner='").append(req.getUinfo().getId()).append("' ");
                sb1.append(" ) ");
            }

            //线索未挖掘
            sb.delete(0, sb.length());
            sb.append("select if(max(a.createTime)<date_sub(now(),INTERVAL 30 day),1,0) flag from clue a where a.isdelete=0 and a.cluebelongtocompany like concat('" + req.getUinfo().getCompanyId() + "','%') ");
            sb.append(" and a.clueowner =  '").append(req.getUinfo().getId()).append("' ");
            sb.append(sb1);
            sb = DBUtils.sql_check(sb);
            List<Map<String, Object>> wwjClueList = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{});
            extData.put("wwjClueCount", wwjClueList.get(0).get("flag"));

            //线索未分配
            sb.delete(0, sb.length());
            sb.append("select count(1) cnt from clue a where a.isdelete=0 and sfzx='1' and a.culestate='02' and a.cluebelongtocompany like concat('" + req.getUinfo().getCompanyId() + "','%') ");
            sb.append(sb1);
            sb = DBUtils.sql_check(sb);
            List<Map<String, Object>> wfpClueList = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{});
            extData.put("wfpClueCount", wfpClueList.get(0).get("cnt"));

            //线索待跟进
            sb.delete(0, sb.length());
            sb.append(" select count(distinct l.uuid) cnt from( ");
            sb.append(" select b.uuid,b.cluebelongtocompany,b.clueowner,b.xzqhdm,max(a.createTime) maxTime,1 type " +
                    " from log_operate a  " +
                    " left join clue b on a.clueId=b.uuid " +
                    " where b.followupstatus!='03' and sfzx='1' AND b.clueowner IS NOT NULL and b.isdelete = '0' ");
            if(!"04".equals(userType)){
                sb.append(" and a.userid='").append(req.getUinfo().getId()).append("' ");
            }
            sb.append(" group by b.uuid ");
            sb.append(" union ");
            sb.append(" select b.uuid,b.cluebelongtocompany,b.clueowner,b.xzqhdm,max(a.modifyTime) maxTime,2 type " +
                    " from log_claim a  " +
                    " left join clue b on a.clueId=b.uuid " +
                    " where b.followupstatus!='03' AND b.clueowner IS NOT NULL and b.isdelete = '0' ");
            if(!"04".equals(userType)){
                sb.append(" and a.userid='").append(req.getUinfo().getId()).append("' ");
            }
            sb.append(" group by b.uuid ) l where l.maxTime<date_sub(curdate(),INTERVAL 30 day) and l.cluebelongtocompany like concat('" + req.getUinfo().getCompanyId() + "','%') ");
            //sb = DBUtils.sql_check(sb) ;
            List<Map<String, Object>> dgjClueList = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{});
            extData.put("dgjClueCount", dgjClueList.get(0).get("cnt"));

            //商机未跟进
            sb.delete(0, sb.length());
            sb.append(" select count(1) cnt from opportunity a where a.isdelete=0 and a.uuid not in (select a.opportunityId from log_claim a where a.isdelete=0 and a.type='03' ) ");
            sb.append(" and a.companyId like concat('" + req.getUinfo().getCompanyId() + "','%') ");
            if(!"04".equals(userType)){
                sb.append(" and a.opportunityowner='").append(req.getUinfo().getId()).append("' ");
            }
            sb = DBUtils.sql_check(sb);
            List<Map<String, Object>> wgjOpportinityList = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{});
            extData.put("wgjOpportinityCount", wgjOpportinityList.get(0).get("cnt"));

            //客户待拜访
//            sb.delete(0, sb.length());
//            sb.append("select count(1) cnt from ( select a.customerId from log_claim a where a.isdelete=0 and a.type='02' ");
//            if(!"04".equals(userType)){
//                sb.append(" and a.userid='").append(req.getUinfo().getId()).append("' ");
//            }
//            sb.append(" group by a.customerId HAVING max(a.modifyTime)<date_sub(now(),INTERVAL 30 day) ) l" );
//            sb = DBUtils.sql_check(sb) ;

            //客户待拜访(以改为线索待跟进)
            sb.delete(0, sb.length());
            sb.append(" select count(distinct l.uuid) cnt from( ");
            sb.append(" select b.uuid,b.cluebelongtocompany,b.clueowner,b.xzqhdm,max(a.createTime) maxTime,1 type " +
                    " from log_operate a  " +
                    " left join clue b on a.clueId=b.uuid " +
                    " where b.followupstatus!='03' and sfzx='1' AND b.clueowner IS NOT NULL and b.isdelete = '0' ");
            if(!"04".equals(userType)){
                sb.append(" and a.userid='").append(req.getUinfo().getId()).append("' ");
            }
            sb.append(" group by b.uuid ");
            sb.append(" union ");
            sb.append(" select b.uuid,b.cluebelongtocompany,b.clueowner,b.xzqhdm,max(a.modifyTime) maxTime,2 type " +
                    " from log_claim a  " +
                    " left join clue b on a.clueId=b.uuid " +
                    " where b.followupstatus!='03' AND b.clueowner IS NOT NULL and b.isdelete = '0' ");
            if(!"04".equals(userType)){
                sb.append(" and a.userid='").append(req.getUinfo().getId()).append("' ");
            }
            sb.append(" group by b.uuid ) l where l.maxTime<date_sub(curdate(),INTERVAL 30 day) and l.cluebelongtocompany like concat('" + req.getUinfo().getCompanyId() + "','%') ");
            //sb = DBUtils.sql_check(sb) ;
            List<Map<String, Object>> dbfCustomerList = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{});
            extData.put("dbfCustomerCount", dbfCustomerList.get(0).get("cnt"));

            //商机无进展
            sb.delete(0, sb.length());
            sb.append(" select count(distinct l.uuid) cnt from( ");
            sb.append(" select b.uuid,b.companyId,b.opportunityowner,b.xzqhdm,max(a.createTime) maxTime,1 type " +
                    " from log_operate a  " +
                    " left join opportunity b on a.opportunityId=b.uuid " +
                    " where b.isdelete = 0 and b.opportunitystage!='06' and b.opportunitystage!='07' ");
            sb.append(" and b.companyId like concat('" + req.getUinfo().getCompanyId() + "','%') ");
            if(!"04".equals(userType)){
                sb.append(" and b.opportunityowner='").append(req.getUinfo().getId()).append("' ");
            }
            sb.append(" group by b.uuid ");
            sb.append(" union  ");
            sb.append(" select b.uuid,b.companyId,b.opportunityowner,b.xzqhdm,max(a.modifyTime) maxTime,2 type " +
                    " from log_claim a  " +
                    " left join opportunity b on a.opportunityId=b.uuid " +
                    " where b.isdelete = 0 and b.opportunitystage!='06' and b.opportunitystage!='07' ");
            sb.append(" and b.companyId like concat('" + req.getUinfo().getCompanyId() + "','%') ");
            if(!"04".equals(userType)){
                sb.append(" and b.opportunityowner='").append(req.getUinfo().getId()).append("' ");
            }
            sb.append(" group by b.uuid ) l where l.maxTime<date_sub(curdate(),INTERVAL 30 day) and l.companyId like concat('" + req.getUinfo().getCompanyId() + "','%') ");
            //sb = DBUtils.sql_check(sb) ;
            List<Map<String, Object>> wjzOpportunityList = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{});
            extData.put("wjzOpportunityCount", wjzOpportunityList.get(0).get("cnt"));
            res.setExtData(extData);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

        }
        return res;
    }

    public PageData getWillFollowUp(DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("获取待跟进线索失败");
        try {
            StringBuffer sb = new StringBuffer();
            StringBuffer sb1 = new StringBuffer();
            //区分是否为管理员 '04'是管理员
            String userType=req.getUinfo().getUserType();
            if(!"04".equals(userType)){
                sb1.append(" and ((1=1  ");
                sb1.append(XzqhUtil.getMultipleQueryXzqh(this.getJdbcTemplate_newg(), req.getUinfo().getAreacode(), "a.xzqhdm"));
                sb1.append(" ) ");
                sb1.append(" or a.clueowner='").append(req.getUinfo().getId()).append("' ");
                sb1.append(" ) ");
                sb1.append("  and a.clueowner =  '").append(req.getUinfo().getId()).append("' ");
            }

            //线索池中有未分配线索
            sb.delete(0, sb.length());
            sb.append(" select * from( ");
            sb.append(" select a.uuid,a.cluecompany cluename,a.cluephone,a.companycontact personincharge,b.modifyTime time " +
                    " from clue a " +
                    " inner join log_claim b on a.uuid=b.clueId " +
                    " where a.sfzx = '1' ");
            sb.append(sb1);
            sb.append(" union ");
            sb.append(" select a.uuid,a.cluecompany  cluename,a.cluephone,a.companycontact personincharge,b.createTime time " +
                    " from clue a " +
                    " inner join log_operate b on a.uuid=b.clueId " +
                    " where a.isdelete<>'1' and a.sfzx='1' ");
            sb.append(sb1);
            sb.append(" ) l group by l.uuid order by l.time desc limit 4");
            sb = DBUtils.sql_check(sb);
            List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{});
            res.setExtData(list);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

        }
        return res;
    }

    public PageData getClueConversion(DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("获取线索转化失败");
        HashMap resData = new HashMap();
        StringBuffer sb = new StringBuffer();
        try {
            String userId = req.getUinfo().getId();
            String companyId = req.getUinfo().getCompanyId();
            String type = (String) param.get("type");
            String dateType = "";
            //01今天   02本周   03 本月
            if ("01".equals(type)) {
                SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
                String dayStart = format.format(new Date()) + " 00:00:00";
                String dayEnd = format.format(new Date()) + " 23:59:59";
                dateType = " and c.createTime >= '" + dayStart + "' and c.createTime <='" + dayEnd + "' ";
            } else if ("02".equals(type)) {
                dateType = " and c.createTime >= '" + getWeekStart() + "' and c.createTime <='" + getWeekEnd() + "' ";
            } else if ("03".equals(type)) {
                dateType = " and c.createTime >= '" + getMonthStart() + "' and c.createTime <='" + getMonthEnd() + "' ";
            }
            //线索总数
            sb.append(" select ");
            sb.append(" count(1) cnt ");
            sb.append(" from clue c ");
            sb.append(" where c.sfzx='1' and c.isdelete='0' and c.cluebelongtocompany like concat(?,'%') ");
            sb.append(dateType);
            //区分是否为管理员 '04'是管理员
            String userType=req.getUinfo().getUserType();
            if(!"04".equals(userType)){
                sb.append(" and c.clueowner = '").append(userId).append("' ");
            }
            sb = DBUtils.sql_check(sb);
            Map<String, Object> stringObjectMap1 = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{companyId}).get(0);
            BigDecimal clueNum = new BigDecimal(((Long) stringObjectMap1.get("cnt")));
            if (!clueNum.equals(BigDecimal.ZERO)) {
                //潜在客户总数
                sb.delete(0, sb.length());
                sb.append(" select ");
                sb.append(" count(1) cnt ");
                sb.append(" from clue c ");
                sb.append(" where c.sfzx='1' and c.isdelete='0' and c.cluebelongtocompany like concat(?,'%') ");
                sb.append(" and c.followupstatus='03' ");
                sb.append(" and c.culestate='02' ");
                sb.append(dateType);
                if(!"04".equals(userType)) {
                    sb.append(" and c.clueowner = '").append(userId).append("' ");
                }
                sb = DBUtils.sql_check(sb);
                Map<String, Object> stringObjectMap2 = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{companyId}).get(0);
                BigDecimal leadsNum = new BigDecimal(((Long) stringObjectMap2.get("cnt")));
                BigDecimal leadsPercentage = leadsNum.divide(clueNum, 2, BigDecimal.ROUND_HALF_UP);
                //线索转为商机总数
                sb.delete(0, sb.length());
                sb.append("select count(1) cnt,IFNULL(sum( amount ),0) je " +
                        "from clue c " +
                        "inner join opportunity o on c.uuid=o.clueId and o.isdelete=0 ");
                sb.append(" where c.sfzx='1' and c.isdelete='0' and c.cluebelongtocompany like concat(?,'%') ");
                sb.append(" and o.opportunitytype = '01' ");
                sb.append(dateType);
                if(!"04".equals(userType)){
                    sb.append(" and c.clueowner = '").append(userId).append("' ");
                }
                sb = DBUtils.sql_check(sb);
                Map<String, Object> stringObjectMap3 = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{companyId}).get(0);
                BigDecimal opportunitiesNum = new BigDecimal(((Long) stringObjectMap3.get("cnt")));
                BigDecimal opportunitiesJe = ((BigDecimal) stringObjectMap3.get("je")).setScale(2, BigDecimal.ROUND_HALF_UP);
                BigDecimal opportunitiesPercentage = opportunitiesNum.divide(clueNum, 2, BigDecimal.ROUND_HALF_UP);
                //线索转为订单总数
                sb.delete(0, sb.length());
                sb.append("select count(1) cnt,IFNULL(sum( contract_amount ),0) je " +
                        "from clue c " +
                        "inner join opportunity o on c.uuid=o.clueId and o.isdelete=0 " +
                        "inner join contract_info ci on ci.opportunity_id=o.uuid");
                sb.append(" where c.sfzx='1' and c.isdelete='0' and c.cluebelongtocompany like concat(?,'%') ");
                sb.append(" and o.opportunitytype = '01' ");
                sb.append(dateType);
                if(!"04".equals(userType)){
                    sb.append(" and c.clueowner = '").append(userId).append("' ");
                }
                sb = DBUtils.sql_check(sb);
                Map<String, Object> stringObjectMap4 = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{companyId}).get(0);
                BigDecimal workOrderNum = new BigDecimal(((Long) stringObjectMap4.get("cnt")));
                BigDecimal workOrderJe = ((BigDecimal) stringObjectMap4.get("je")).setScale(2, BigDecimal.ROUND_HALF_UP);
                BigDecimal workOrderPercentage = workOrderNum.divide(clueNum, 2, BigDecimal.ROUND_HALF_UP);
                sb.delete(0, sb.length());
                sb.append("select IFNULL(sum( contract_amount ),0) je " +
                        "from clue c " +
                        "inner join opportunity o on c.uuid=o.clueId and o.isdelete=0 " +
                        "inner join contract_info ci on ci.opportunity_id=o.uuid " +
                        "left join t_project tp on tp.contract_no=ci.id and is_payment = '1'");
                sb.append(" where c.sfzx='1' and c.isdelete='0' and c.cluebelongtocompany like concat(?,'%') ");
                sb.append(" and o.opportunitytype = '01' ");
                sb.append(dateType);
                if(!"04".equals(userType)){
                    sb.append(" and c.clueowner = '").append(userId).append("' ");
                }
                sb = DBUtils.sql_check(sb);
                Map<String, Object> stringObjectMap5 = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{companyId}).get(0);
                BigDecimal workOrderHk = ((BigDecimal) stringObjectMap5.get("je")).setScale(2, BigDecimal.ROUND_HALF_UP);
                resData.put("clueNum", clueNum);
                resData.put("leadsNum", leadsNum);
                resData.put("leadsPercentage", leadsPercentage);
                resData.put("opportunitiesNum", opportunitiesNum);
                resData.put("opportunitiesPercentage", opportunitiesPercentage);
                resData.put("opportunitiesJe", opportunitiesJe);
                resData.put("workOrderNum", workOrderNum);
                resData.put("workOrderPercentage", workOrderPercentage);
                resData.put("workOrderJe", workOrderJe);
                resData.put("workOrderHk", workOrderHk);
            } else {
                resData.put("clueNum", clueNum);
                resData.put("leadsNum", 0);
                resData.put("leadsPercentage", 0);
                resData.put("opportunitiesNum", 0);
                resData.put("opportunitiesPercentage", 0);
                resData.put("opportunitiesJe", 0);
                resData.put("workOrderNum", 0);
                resData.put("workOrderPercentage", 0);
                resData.put("workOrderJe", 0);
                resData.put("workOrderHk", 0);
            }
            res.setData(resData);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return res;
    }

    public PageData getRankingList(DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("获取排行榜失败");
        StringBuffer sb = new StringBuffer();
        try {
            String companyId = req.getUinfo().getCompanyId();
            String orderBy = (String) param.get("orderBy");
            String type = (String) param.get("type");
            String time = (String) param.get("time");
            String week = (String) param.get("week");
            String limit = (String) param.get("limit");
            String dateType = "";
            //01今天   02本周   03 本月
            if ("01".equals(type)) {
                dateType = " and c.createTime like concat('" + time + "','%') ";
            } else if ("02".equals(type)) {
                dateType = " and c.createTime >= '" + getStartDayOfWeekNo(Integer.parseInt(time), Integer.parseInt(week)) + " 00:00:00' and c.createTime <='" + getEndDayOfWeekNo(Integer.parseInt(time), Integer.parseInt(week)) + " 23:59:59' ";
            } else if ("03".equals(type)) {
                dateType = " and c.createTime like concat('" + time + "','%') ";
            }
            //01跟进记录数排序 02商机数排序 03客户数排序
            if ("01".equals(orderBy)) {
                sb.append("SELECT count(1) cnt,tu.nickName " +
                        "from t_user tu " +
                        "left join clue c on c.clueowner=tu.id and c.sfzx='1' and c.isdelete='0' and c.cluebelongtocompany like concat(?,'%')" +
                        "left join log_claim lc on lc.clueId=c.uuid ");
                sb.append(" where tu.sfsc = 0 ");
                sb.append(dateType);
                sb.append(" GROUP BY c.clueowner ORDER BY cnt desc");
                sb.append(" limit ").append(limit);
            } else if ("02".equals(orderBy)) {
                sb.append("SELECT count(1) cnt,tu.nickName " +
                        "from t_user tu " +
                        "left join clue c on c.clueowner=tu.id and c.sfzx='1' and c.isdelete='0' and c.cluebelongtocompany like concat(?,'%')" +
                        "left JOIN opportunity o ON o.clueId = c.uuid ");
                sb.append(" where tu.sfsc = 0 ");
                sb.append(dateType);
                sb.append(" GROUP BY c.clueowner ORDER BY cnt desc");
                sb.append(" limit ").append(limit);
            } else if ("03".equals(orderBy)) {
                sb.append("SELECT count(1) cnt,tu.nickName " +
                        "from t_user tu " +
                        "left join clue c on c.clueowner=tu.id and c.sfzx='1' and c.isdelete='0' and c.cluebelongtocompany like concat(?,'%') and c.culestate='02' and c.followupstatus='03' ");
                sb.append(" where tu.sfsc = 0 ");
                sb.append(dateType);
                sb.append(" GROUP BY c.clueowner ORDER BY cnt desc");
                sb.append(" limit ").append(limit);
            }

            sb = DBUtils.sql_check(sb);
            List<Map<String, Object>> mapList = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{companyId});
            for (int i = 0; i < mapList.size(); i++) {
                Map<String, Object> map = mapList.get(i);
                map.put("no", i + 1);
            }
            res.setPage(mapList);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return res;
    }

    public PageData saveSchedule(DataReq req) throws Exception {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("保存日程失败");
        Connection conn = null;
        StringBuffer sb = new StringBuffer();
        try {
            conn = DBUtils.getJNDIDBConn();

            String userId = req.getUinfo().getId(); // 用户ID
            String companyId = req.getUinfo().getCompanyId(); // 公司ID
            Integer id = (Integer) req.getReqData().get("id"); // 主键ID
            String title = (String) req.getReqData().get("title"); // 日程标题
            String type = StrUtil.isEmpty((String) req.getReqData().get("type")) ? null : (String) req.getReqData().get("type"); // 日程类型
            String address = (String) req.getReqData().get("address"); // 地址
            String startTime = (String) req.getReqData().get("startTime"); // 开始时间
            String endTime = (String) req.getReqData().get("endTime"); // 结束时间
            String remindTime = (String) req.getReqData().get("remindTime"); // 提醒时间
            String isAllDay = (String) req.getReqData().get("isAllDay"); // 全天
            String isPrivate = (String) req.getReqData().get("isPrivate"); // 私密
            String relatedType = (String) req.getReqData().get("relatedType"); // 关联业务
            String related = (String) req.getReqData().get("related"); // 关联业务内容
            String isRepeat = StrUtil.isEmpty((String) req.getReqData().get("isRepeat")) ? null : (String) req.getReqData().get("isRepeat"); // 重复
            String ossimg = (String) req.getReqData().get("ossimg"); // 图片
            String memo = (String) req.getReqData().get("memo"); // 备注
            List<String> players = (List<String>) req.getReqData().get("players"); // 参与人
            String longitude = null;
            String latitude = null;
            if (StrUtil.isNotEmpty(address)) {
                JSONObject geoByAddress = MapUtil.getGeoByAddress(address);
                if (ObjectUtil.isNotEmpty(geoByAddress)) {
                    String[] locations = geoByAddress.getString("location").split(",");
                    longitude = locations[0];// 经度
                    latitude = locations[1]; // 纬度
                }
            }

            Date date = new Date();
            Object[] condition;
            if (ObjectUtil.isEmpty(id)) {
                sb.append("insert into t_programme (userid, companyId, title, type, address, longitude, latitude, starttime, endtime, isallday, remindtime, related, relatedtype, isprivate, isrepeat, ossimg, memo, deleted, createUid, createTime)\n" +
                        "values  (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0, ?, ?)");
                condition = new Object[]{userId, companyId, title, type, address, longitude, latitude, startTime, endTime, isAllDay, remindTime, related, relatedType, isPrivate, isRepeat, ossimg, memo, userId, date};
            } else {
                sb.append("update t_programme set ");
                sb.append(StrUtil.isNotEmpty(userId) ? "userId = '" + userId + "', " : "");
                sb.append(StrUtil.isNotEmpty(companyId) ? "companyId = '" + companyId + "', " : "");
                sb.append(StrUtil.isNotEmpty(title) ? "title = '" + title + "', " : "");
                sb.append(StrUtil.isNotEmpty(type) ? "type = '" + type + "', " : "");
                sb.append(StrUtil.isNotEmpty(address) ? "address = '" + address + "', " : "");
                sb.append(StrUtil.isNotEmpty(longitude) ? "longitude = " + longitude + ", " : "");
                sb.append(StrUtil.isNotEmpty(latitude) ? "latitude = " + latitude + ", " : "");
                sb.append(StrUtil.isNotEmpty(startTime) ? "startTime = '" + startTime + "', " : "");
                sb.append(StrUtil.isNotEmpty(endTime) ? "endTime = '" + endTime + "', " : "");
                sb.append(StrUtil.isNotEmpty(remindTime) ? "remindTime = '" + remindTime + "', " : "");
                sb.append(StrUtil.isNotEmpty(isAllDay) ? "isAllDay = " + isAllDay + ", " : "");
                sb.append(StrUtil.isNotEmpty(isPrivate) ? "isPrivate = " + isPrivate + ", " : "");
                sb.append(StrUtil.isNotEmpty(relatedType) ? "relatedType = '" + relatedType + "', " : "");
                sb.append(StrUtil.isNotEmpty(related) ? "related = '" + related + "', " : "");
                sb.append(StrUtil.isNotEmpty(isRepeat) ? "isRepeat = '" + isRepeat + "', " : "");
                sb.append(StrUtil.isNotEmpty(ossimg) ? "ossimg = '" + ossimg + "', " : "");
                sb.append(StrUtil.isNotEmpty(memo) ? "memo = '" + memo + "', " : "");
                sb.append("modifyUid = ?,");
                sb.append("modifyTime = ?");
                sb.append("where id =? ");
                condition = new Object[]{userId, date, id};
            }

            String id2 = String.valueOf(this.updateReutrnId(conn, sb.toString(), condition));

            if (ObjectUtil.isNotEmpty(id)) {
                sb.delete(0, sb.length());
                sb.append("delete from t_programme_join where deleted = 0 and programmeid = ?");
                this.update(conn, sb.toString(), new Object[]{id});
            }

            for (String player : players) {
                sb.delete(0, sb.length());
                sb.append("insert into t_programme_join (programmeid, userid, createUid, createTime, deleted) values (?,?,?,?,0)");
                this.update(conn, sb.toString(), new Object[]{ObjectUtil.isNotEmpty(id) ? id : id2, player, userId, date});
            }


            res.setRetCode(0);
            res.setRetMessage("ok");
            conn.commit();
        } finally {
            if (conn != null) {
                conn.close();
            }
        }
        return res;
    }

    public PageData getScheduleList(DataReq req) throws Exception {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("查询日程失败");
        Connection conn = null;
        StringBuffer sb = new StringBuffer();
        try {
            conn = DBUtils.getJNDIDBConn();

            String userId = req.getUinfo().getId(); // 用户ID
            String date = (String) req.getReqData().get("date"); // 日期
            String starttime = (String) req.getReqData().get("starttime"); // 开始时间  列表查询时使用
            String endtime = (String) req.getReqData().get("endtime"); // 结束时间  列表查询时使用
            String id = (String) req.getReqData().get("id"); // 日程ID
            String title = (String) req.getReqData().get("title"); // 日程ID
            sb.append("select (select group_concat(t.userid) from t_programme_join t where t.deleted = 0 and t.programmeid = a.id) as players,\n" +
                    "       (select group_concat(u.nickName) from t_programme_join t inner join t_user u on u.id = t.userid where t.deleted = 0 and t.programmeid = a.id) as playerNames,\n" +
                    " date_format(a.starttime, '%Y-%m-%d %T') as startTime,date_format(a.endtime, '%Y-%m-%d %T') as endTime,\n" +
                    " a.id, a.companyId, a.userid, a.title, a.address, a.isallday as isAllDay, a.remindtime as remindTime , a.related, a.relatedtype as relatedType,\n" +
                    " a.isprivate as isPrivate, a.isrepeat as isRepeat, a.ossimg, a.memo, a.longitude, a.latitude,\n" +
                    " d.name as relatedTypeName, e.name as typeName, f.name as repeatName, g.name as remindName,\n" +
                    " if(a.relatedtype = '02', c.opportunityname , concat_ws('-',b.cluename,b.cluephone,b.companycontact)) as relatednr, a.type\n" +
                    " from t_programme a\n" +
                    " left join clue b on b.uuid = a.related\n" +
                    " left join opportunity c on c.uuid = a.related\n" +
                    " left join t_param d on d.value = a.relatedtype and d.param_type = '139'\n" +
                    " left join t_param e on e.value = a.type and e.param_type = '138'\n" +
                    " left join t_param f on f.value = a.isrepeat and f.param_type = '137'\n" +
                    " left join t_param g on g.value = a.remindtime and g.param_type = '136' \n" +
                    " where a.deleted = 0 and a.createUid =  ?");
            sb.append(StrUtil.isNotEmpty(title) ? " and a.title like concat(%, '" + title + "', %)" : "");
            sb.append(ObjectUtil.isNotEmpty(date) ? " and date_format('" + date + "','%Y-%m-%d') between date_format(a.starttime,'%Y-%m-%d') and date_format(a.endtime,'%Y-%m-%d') " : "");
            if (ObjectUtil.isEmpty(date)) {
                if (ObjectUtil.isNotEmpty(starttime) && ObjectUtil.isNotEmpty(endtime)) {
                    sb.append(" and (a.starttime between '"+ starttime +"' and '" + endtime + "' or a.endtime between '" + starttime +"' and '" + endtime + "')");
                } else {
                    sb.append(" and (a.starttime between DATE_FORMAT(CURDATE(),'%Y-%m-%d %T') and date_add(DATE_FORMAT(CURDATE(),'%Y-%m-%d %T'), interval 7 day) or a.endtime between DATE_FORMAT(CURDATE(),'%Y-%m-%d %T') and date_add(DATE_FORMAT(CURDATE(),'%Y-%m-%d %T'), interval 7 day))");
                }
            }
            sb.append(ObjectUtil.isNotEmpty(id) ? " and a.id = " + id : "");
            sb.append(" order by a.starttime desc ");
            StringBuffer sbfrom = new StringBuffer();
            sbfrom.append(sb.toString());
            int idx = sbfrom.lastIndexOf(" from ");
            sbfrom.delete(0, idx);
            Page page = new Page(conn, sbfrom.toString(), req.getPageid(), req.getPagesize(), res, new Object[]{userId});
            sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
            List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{userId});
            if (CollUtil.isNotEmpty(list)) {
                for (Map<String, Object> map : list) {
                    String[] players = ((String) map.get("players")).split(",");
                    map.put("players", players);
                    String ossimg = (String) map.get("ossimg");
                    if (StrUtil.isNotBlank(ossimg)) {
                        String[] split = ossimg.split(",");
                        List<String> imags = new ArrayList<>();
                        for (String s : split) {
                            imags.add(OssUtil.getImgSignURL(s, 0));
                        }
                        map.put("imgs", imags);
                    }
                }
                res.setPage(list);
            } else {
                res.setPage(list);
            }
            res.setRetCode(0);
            res.setRetMessage("ok");
        } finally {
            if (conn != null) {
                conn.close();
            }
        }
        return res;
    }

    public PageData deletedSchedule(DataReq req) throws Exception {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("删除日程失败");
        Connection conn = null;
        StringBuffer sb = new StringBuffer();
        try {
            conn = DBUtils.getJNDIDBConn();

            List<Integer> ids = (List<Integer>) req.getReqData().get("ids");

            sb.append("update t_programme set deleted = 1 where id in (");
            for (Integer id : ids) {
                sb.append(id + ",");
            }
            sb.delete(sb.length() - 1, sb.length());
            sb.append(")");
            update(conn, sb.toString(), new Object[]{});

            sb.delete(0, sb.length());
            sb.append("update t_programme_join set deleted = 1 where programmeid in (");
            for (Integer id : ids) {
                sb.append(id + ",");
            }
            sb.delete(sb.length() - 1, sb.length());
            sb.append(")");
            update(conn, sb.toString(), new Object[]{});


            res.setRetCode(0);
            res.setRetMessage("ok");
            conn.commit();
        } finally {
            if (conn != null) {
                conn.close();
            }
        }
        return res;
    }

    public PageData getRelated(DataReq req) throws Exception {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("查询关联业务信息失败");
        Connection conn = null;
        StringBuffer sb = new StringBuffer();
        try {
            conn = DBUtils.getJNDIDBConn();
            String companyId = req.getUinfo().getCompanyId();
            String relateType = (String) req.getReqData().get("relateType");

            if ("01".equals(relateType)) {
                sb.append("select * from clue");
            }
            if ("02".equals(relateType)) {

            }
            if ("03".equals(relateType)) {

            }

            res.setRetCode(0);
            res.setRetMessage("ok");
        } finally {
            if (conn != null) {
                conn.close();
            }
        }
        return res;
    }


}
